package com.matt.repository.record;

import com.matt.domain.record.LiveRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/8/3
 * *******************************
 */
public interface LiveRecordRepository extends JpaRepository<LiveRecord, String> , JpaSpecificationExecutor<LiveRecord> {

    LiveRecord getByAreaIdAndId(String areaId, String id);

    List<LiveRecord> findByOperatorIdAndDeletedAndCreatedBetween(String staffId, Boolean deleted, Date begin, Date end);

    List<LiveRecord> findByOperatorIdAndDeleted(String staffId, Boolean deleted);

    @Query(value = "SELECT l.*  " +
            "FROM live_record l " +
            "LEFT JOIN rel_children_live_record clr on clr.liveRecordId = l.id " +
            "WHERE l.areaId = ?1 " +
            "AND l.mediaType = 1 and l.resourceUrl is not null " +
            "AND clr.childrenId=?2 " +
            "ORDER BY l.created desc LIMIT ?3,?4",
            nativeQuery = true)
    List<LiveRecord> findAllValidPhotos(String areaId, String childrenId, Integer beginRow, Integer size);

    @Query(value = "SELECT count(*) " +
            "FROM live_record l " +
            "LEFT JOIN rel_children_live_record clr on clr.liveRecordId = l.id " +
            "WHERE l.areaId = ?1 " +
            "AND l.mediaType = 1 and l.resourceUrl is not null " +
            "AND clr.childrenId=?2 ",
            nativeQuery = true)
    Integer countValidPhotos(String areaId, String childrenId);
}
